﻿using StackExchange.Redis;
using TeachlyQBank.DbPool;
using TeachlyQBank.Models;
using NLog;
using Microsoft.AspNetCore.Http;
using System.Data;

namespace TeachlyQBank.DbServer
{
    public class PaperServer
    {
        private readonly MySqlConnectionAdapter _mysql; // 操作mysql的类
        private readonly OracleConnectionAdapter _oracle; // 操作oracle的类
        private readonly Logger _logger;

        public PaperServer(MySqlConnectionAdapter mysql, OracleConnectionAdapter oracle)
        {
            _mysql = mysql;
            _logger = LogManager.GetCurrentClassLogger();
            _oracle = oracle;
        }

        // 添加一个试卷
        public int AddPaper(Paper paper)
        {
            _mysql.Open();
        
            string query = "insert into T_PAPER(paper_id,paper_name) " +
            "values(@paper_id,@paper_name)";

            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@paper_id", paper.paper_id },
                { "@paper_name",paper.paper_name},
            };

            int count = _mysql.ExecuteNonQuery(query, parameters);

            if (count > 0)
            {
                _logger.Info($"添加了一个新的试卷:{paper.paper_name},本次花费了{_mysql.GetLastExecutionTime()}毫秒");
                _mysql.Close();
                return count;
            }
            else
            {
                _logger.Error($"添加知识点出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                _mysql.Close();
                return -1;
            }
        }

        // 为一个试卷添加一个问题
        public int AddPaperQuestion(Paper_Question paper_Question)
        {
            _mysql.Open();

            string query = "insert into T_PAPER_QUESTION(relation_id,paper_id,question_id) " +
            "values(@relation_id,@paper_id,@question_id)";

            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@relation_id", paper_Question.relation_id },
                { "@paper_id", paper_Question.paper_id},
                { "@question_id", paper_Question.question_id},
            };

            int count = _mysql.ExecuteNonQuery(query, parameters);

            if (count > 0)
            {
                _logger.Info($"添加了一个新的试卷题型关联,本次花费了{_mysql.GetLastExecutionTime()}毫秒");
                _mysql.Close();
                return count;
            }
            else
            {
                _logger.Error($"添加知识点出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                _mysql.Close();
                return -1;
            }
        }

        // 查询所有试卷
        public List<Paper> GetAllPapers()
        {
            string query = "SELECT paper_id, paper_name, paper_status FROM T_PAPER";

            var papers = new List<Paper>();
            DataSet ds = _mysql.ExecuteQuery(query);

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    papers.Add(new Paper
                    {
                        paper_id = row["paper_id"].ToString(),
                        paper_name = row["paper_name"].ToString(),
                        paper_status = row["paper_status"].ToString(),
                    });
                }
                _logger.Info($"成功获取了所有的试卷,本次花费了{_mysql.GetLastExecutionTime()}毫秒");
            }
            else
            {
                _logger.Error($"获取试卷列表出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
            }
            return papers;
        }

        // 查询所有关联的试题和问题数据
        public List<ViewModel_JudgePaper> GetAllPQ(string paper_id)
        {
            string query = "SELECT T_QUESTION.question_id,T_QUESTION.question_content,T_QUESTION.question_difficulty,T_QUESTION.question_type," +
                "T_QUESTION.answer\r\nFROM T_PAPER_QUESTION \r\nLEFT JOIN T_QUESTION \r\nON T_QUESTION.question_id" +
                " = T_PAPER_QUESTION.question_id\r\nWHERE T_PAPER_QUESTION.paper_id=@paper_id;";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                {"@paper_Id",paper_id !},
            };

            var papers = new List<ViewModel_JudgePaper>();
            DataSet ds = _mysql.ExecuteQuery(query,parameters);

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    papers.Add(new ViewModel_JudgePaper
                    {
                        question_id = row["question_id"].ToString(),
                        question_content = row["question_content"].ToString(),
                        question_difficulty = row["question_difficulty"].ToString(),
                        answer = row["answer"].ToString(),
                        question_type = row["question_type"].ToString(),
                    }) ; 
                }
            }
            else
            {
                _logger.Error($"获取试卷问题列表出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
            }

            return papers;
        }
    }

}
